﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using JKF.Substructure;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;

namespace JKF.DataModule.SqlServerDAL
{
   public partial class dalGIFTS:IDAL.IdalGIFTS
    {

        // 定义要获取哪些字段 GIFT_KEY, GIFT_TPYE_KEY, NAME, NEED_POINT, MEMO, USER_CREATE, DATE_CREATE, DATE_UPDATE, VALIDITY_PERIOD, SHOP_CODE 
       private const string FieldShow = "GIFT_KEY, [dbo].[getGift_TypeName](GIFT_TPYE_KEY) AS GIFT_TPYE_KEY, NAME, NEED_POINT, MEMO, USER_CREATE, DATE_CREATE, DATE_UPDATE, VALIDITY_PERIOD, SHOP_CODE  ";
        private const string FieldOrder = "NAME";
        private const string FieldKey = "GIFT_KEY";
        /// <summary>
        /// 获取礼品列表
        /// </summary>
        /// <param name="NAME">礼品名称</param>
        /// <param name="pageCurrent">当前页码</param>
        /// <param name="pageSize">一页显示多少</param>
        /// <param name="pageCount">总页数</param>
        /// <param name="rowCount">总记录数</param>
        /// <returns>返回DataTable数据</returns>
        public static DataTable GetGiftsDAL(string NAME,string NEED_POINT_min, string NEED_POINT_max , int pageCurrent, int pageSize, ref int pageCount, ref int rowCount)
        {
            var sql = new StringBuilder(@"  1=1 ");
            // 下面组装查询条件
            if (!string.IsNullOrEmpty(NAME))
            {
                sql.AppendFormat(" AND NAME  LIKE '%{0}%'", NAME);
            }
            // 如果指定了最大和最小值，就按照区间查询
            if (!string.IsNullOrEmpty(NEED_POINT_min) && !string.IsNullOrEmpty(NEED_POINT_max))
            {
                sql.AppendFormat(" AND NEED_POINT  BETWEEN {0} AND {1}", Convert.ToDecimal(NEED_POINT_min), Convert.ToDecimal(NEED_POINT_max));
            }
            else if (!string.IsNullOrEmpty(NEED_POINT_min))
            {
                sql.AppendFormat(" AND NEED_POINT > {0}", Convert.ToDecimal(NEED_POINT_min));
            }
            else if (!string.IsNullOrEmpty(NEED_POINT_max))
            {
                sql.AppendFormat(" AND NEED_POINT < {0}", Convert.ToDecimal(NEED_POINT_max));
            }
            // 下面调用此方法去查询数据库
            return DataPage.GetPageInfo("dbo.GIFTS", FieldKey, pageCurrent, pageSize, FieldShow, FieldOrder, sql.ToString(), ref pageCount, ref rowCount).Tables[0];

        }


        /// <summary>
        /// 判断是否存在此名称,true表示存在
        /// </summary>
        /// <param name="shop_name">店面名称</param>
        /// <returns></returns>
        public static bool ExistsGiftsNameDAL(string gift_name)
        {
            object obj = DB.HR.ExecuteScalar(CommandType.Text, "select count(1) from dbo.GIFTS where NAME='" + gift_name + "'");
            return Convert.ToInt32(obj) > 0;
        }
        /// <summary>
        /// 事务删除礼品 
        /// </summary>
        /// <param name="gift_key">礼品  ID</param>
        /// <returns></returns>
        public static string TransactionSQLDeleteDAL( Guid gift_key)
        {
            int result = -1;
            Database db = DB.HR;
            DbCommand dbCommand = db.GetStoredProcCommand("[dbo].[proc_GIFTDelete]");
            db.AddInParameter(dbCommand, "@gift_key", DbType.Guid, gift_key);
           // db.AddInParameter(dbCommand, "@user_key", DbType.Guid, user_key);
            db.AddOutParameter(dbCommand, "@result", DbType.Int32, result);
            db.ExecuteNonQuery(dbCommand);
            return dbCommand.Parameters["@result"].Value.ToString();
        }
    }
}
